package com.scau.hwadee.examination.utils;

import com.scau.hwadee.examination.entity.Course;
import com.scau.hwadee.examination.entity.Major;
import com.scau.hwadee.examination.entity.Nationwidecourse;
import com.scau.hwadee.examination.entity.Nationwidemajor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.List;

/**
 * @author YHX
 * @description 导出Excel表
 * @date 2019/7/30 16:54
 **/
@Slf4j
public class ExportExcelUtils {

  /**
   * @return org.springframework.http.ResponseEntity<byte [ ]>
   * @author YHX
   * @date 2019/7/29  10:59
   * @params [nationwidemajors]
   * @description 导出全国专业信息表
   */
  public static ResponseEntity<byte[]> exportNationwideMajor2Excel(List<Nationwidemajor> nationwidemajors) {
    log.debug("" + nationwidemajors);
    HttpHeaders headers = null;
    ByteArrayOutputStream baos = null;
    try {
      //1.创建Excel文档
      HSSFWorkbook workbook = new HSSFWorkbook();
      //2.创建文档摘要
      workbook.createInformationProperties();
      //3.获取文档信息，并配置
      DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
      //3.1文档类别
      dsi.setCategory("全国专业信息");
      //3.2设置文档管理员
      dsi.setManager("Hachiko");
      //3.3设置组织机构
      dsi.setCompany("四川省成都市华迪软件信息有限公司");
      //4.获取摘要信息并配置
      SummaryInformation si = workbook.getSummaryInformation();
      //4.1设置文档主题
      si.setSubject("全国专业信息表");
      //4.2.设置文档标题
      si.setTitle("全国专业信息");
      //4.3 设置文档作者
      si.setAuthor("Hachiko");
      //4.4设置文档备注
      si.setComments("暂无");
      //创建Excel表单
      HSSFSheet sheet = workbook.createSheet("全国专业信息表");
      //创建日期显示格式
      HSSFCellStyle dateCellStyle = workbook.createCellStyle();
      dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
      //创建标题的显示样式
      HSSFCellStyle headerStyle = workbook.createCellStyle();
      headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
      headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
      //定义列的宽度
      sheet.setColumnWidth(0, 15 * 256);
      sheet.setColumnWidth(1, 14 * 256);
      sheet.setColumnWidth(2, 10 * 256);
      sheet.setColumnWidth(3, 8 * 256);
      //5.设置表头
      HSSFRow headerRow = sheet.createRow(0);
      HSSFCell cell0 = headerRow.createCell(0);
      cell0.setCellValue("国家专业代码");
      cell0.setCellStyle(headerStyle);
      HSSFCell cell1 = headerRow.createCell(1);
      cell1.setCellValue("专业名称");
      cell1.setCellStyle(headerStyle);
      HSSFCell cell2 = headerRow.createCell(2);
      cell2.setCellValue("学历层次");
      cell2.setCellStyle(headerStyle);
      HSSFCell cell3 = headerRow.createCell(3);
      cell3.setCellValue("学分");
      cell3.setCellStyle(headerStyle);
      //6.装数据
      for (int i = 0; i < nationwidemajors.size(); i++) {
        HSSFRow row = sheet.createRow(i + 1);
        Nationwidemajor nationwidemajor = nationwidemajors.get(i);
        row.createCell(0).setCellValue(nationwidemajor.getNmId());
        row.createCell(1).setCellValue(nationwidemajor.getNmName());
        row.createCell(2).setCellValue(nationwidemajor.getNmLevel());
        row.createCell(3).setCellValue(nationwidemajor.getNmCredit());
      }
      headers = new HttpHeaders();
      headers.setContentDispositionFormData("attachment",
        new String("NationalWideMajorTable.xls".getBytes(StandardCharsets.UTF_8),
          StandardCharsets.ISO_8859_1));
      headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
      baos = new ByteArrayOutputStream();
      workbook.write(baos);
    } catch (IOException e) {
      e.printStackTrace();
    }
    return new ResponseEntity<>(baos.toByteArray(), headers, HttpStatus.CREATED);
  }

  /**
   * @return org.springframework.http.ResponseEntity<byte [ ]>
   * @author YHX
   * @description 导出四川省的专业为Excel表
   * @date 16:55 2019/7/30
   * @Param [majors]
   **/
  public static ResponseEntity<byte[]> exportSCProvinceMajor2Excel(List<Major> majors) {
    HttpHeaders headers = null;
    ByteArrayOutputStream baos = null;
    try {
      //1.创建Excel文档
      HSSFWorkbook workbook = new HSSFWorkbook();
      //2.创建文档摘要
      workbook.createInformationProperties();
      //3.获取文档信息，并配置
      DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
      //3.1文档类别
      dsi.setCategory("全国专业信息");
      //3.2设置文档管理员
      dsi.setManager("Hachiko");
      //3.3设置组织机构
      dsi.setCompany("四川省成都市华迪软件信息有限公司");
      //4.获取摘要信息并配置
      SummaryInformation si = workbook.getSummaryInformation();
      //4.1设置文档主题
      si.setSubject("四川省专业信息表");
      //4.2.设置文档标题
      si.setTitle("四川省专业信息");
      //4.3 设置文档作者
      si.setAuthor("Hachiko");
      //4.4设置文档备注
      si.setComments("暂无");
      //创建Excel表单
      HSSFSheet sheet = workbook.createSheet("四川省专业信息表");
      //创建日期显示格式
      HSSFCellStyle dateCellStyle = workbook.createCellStyle();
      dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
      //创建标题的显示样式
      HSSFCellStyle headerStyle = workbook.createCellStyle();
      headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
      headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
      //定义列的宽度
      for (int i = 0; i <= 24; i++) {
        sheet.setColumnWidth(i, 15 * 256);
      }
      //5.设置表头
      HSSFRow headerRow = sheet.createRow(0);
      HSSFCell cell0 = headerRow.createCell(0);
      cell0.setCellValue("专业编码");
      cell0.setCellStyle(headerStyle);
      HSSFCell cell1 = headerRow.createCell(1);
      cell1.setCellValue("国家专业代码");
      cell1.setCellStyle(headerStyle);
      HSSFCell cell2 = headerRow.createCell(2);
      cell2.setCellValue("专业名称");
      cell2.setCellStyle(headerStyle);
      HSSFCell cell3 = headerRow.createCell(3);
      cell3.setCellValue("专业简介");
      cell3.setCellStyle(headerStyle);
      HSSFCell cell4 = headerRow.createCell(4);
      cell4.setCellValue("专业类型");
      cell4.setCellStyle(headerStyle);
      HSSFCell cell5 = headerRow.createCell(5);
      cell5.setCellValue("学历层次");
      cell5.setCellStyle(headerStyle);
      HSSFCell cell6 = headerRow.createCell(6);
      cell6.setCellValue("开考形式");
      cell6.setCellStyle(headerStyle);
      HSSFCell cell7 = headerRow.createCell(7);
      cell7.setCellValue("主考院校");
      cell7.setCellStyle(headerStyle);
      HSSFCell cell8 = headerRow.createCell(8);
      cell8.setCellValue("首次开考批次");
      cell8.setCellStyle(headerStyle);
      HSSFCell cell9 = headerRow.createCell(9);
      cell9.setCellValue("批准文号");
      cell9.setCellStyle(headerStyle);
      HSSFCell cell10 = headerRow.createCell(10);
      cell10.setCellValue("停止新生注册考次");
      cell10.setCellStyle(headerStyle);
      HSSFCell cell11 = headerRow.createCell(11);
      cell11.setCellValue("停止注册文号");
      cell11.setCellStyle(headerStyle);
      HSSFCell cell12 = headerRow.createCell(12);
      cell12.setCellValue("停止报考考次");
      cell12.setCellStyle(headerStyle);
      HSSFCell cell13 = headerRow.createCell(13);
      cell13.setCellValue("停止报考文号");
      cell13.setCellStyle(headerStyle);
      HSSFCell cell14 = headerRow.createCell(14);
      cell14.setCellValue("停止颁发毕业证日期");
      cell14.setCellStyle(headerStyle);
      HSSFCell cell15 = headerRow.createCell(15);
      cell15.setCellValue("停止颁发文号");
      cell15.setCellStyle(headerStyle);
      HSSFCell cell16 = headerRow.createCell(16);
      cell16.setCellValue("专业状态");
      cell16.setCellStyle(headerStyle);
      HSSFCell cell17 = headerRow.createCell(17);
      cell17.setCellValue("总学分");
      cell17.setCellStyle(headerStyle);
      HSSFCell cell18 = headerRow.createCell(18);
      cell18.setCellValue("毕业学分");
      cell18.setCellStyle(headerStyle);
      HSSFCell cell19 = headerRow.createCell(19);
      cell19.setCellValue("总课程数");
      cell19.setCellStyle(headerStyle);
      HSSFCell cell20 = headerRow.createCell(20);
      cell20.setCellValue("是否分方向");
      cell20.setCellStyle(headerStyle);
      HSSFCell cell21 = headerRow.createCell(21);
      cell21.setCellValue("专业大类");
      cell21.setCellStyle(headerStyle);
      HSSFCell cell22 = headerRow.createCell(22);
      cell22.setCellValue("报考条件说明");
      cell22.setCellStyle(headerStyle);
      HSSFCell cell23 = headerRow.createCell(23);
      cell23.setCellValue("毕业条件说明");
      cell23.setCellStyle(headerStyle);
      HSSFCell cell24 = headerRow.createCell(24);
      cell24.setCellValue("备注");
      cell24.setCellStyle(headerStyle);
      //6.装数据
      for (int i = 0; i < majors.size(); i++) {
        HSSFRow row = sheet.createRow(i + 1);
        Major major = majors.get(i);
        row.createCell(0).setCellValue(major.getMjId());
        row.createCell(1).setCellValue(major.getNmId());
        row.createCell(2).setCellValue(major.getMjName());
        row.createCell(3).setCellValue(major.getMjIntro());
        row.createCell(4).setCellValue(major.getMjType());
        row.createCell(5).setCellValue(major.getMjLevel());
        row.createCell(6).setCellValue(major.getMjExamform());
        row.createCell(7).setCellValue(major.getMjFirstexaminer());
        row.createCell(8).setCellValue(major.getMjFirstes());
        row.createCell(9).setCellValue(major.getMjApprnum());
        HSSFCell hssfCell = row.createCell(10);
        if (major.getMjStopnbes() == null) {
          hssfCell.setCellValue("");
        } else {
          hssfCell.setCellValue(major.getMjStopnbes());
        }
        HSSFCell hssfCell1 = row.createCell(11);
        if (major.getMjStopes() == null) {
          hssfCell.setCellValue("");
        } else {
          hssfCell1.setCellValue(major.getMjStopes());
        }
        HSSFCell hssfCell2 = row.createCell(12);
        if (major.getMjStopreges() == null) {
          hssfCell2.setCellValue("");
        } else {
          hssfCell2.setCellValue(major.getMjStopreges());
        }
        HSSFCell hssfCell3 = row.createCell(13);
        if (major.getMjStopregnum() == null) {
          hssfCell3.setCellValue("");
        } else {
          hssfCell3.setCellValue(major.getMjStopregnum());
        }
        HSSFCell hssfCell4 = row.createCell(14);
        if (major.getMjStopawdip() == null) {
          hssfCell4.setCellValue("");
        } else {
          hssfCell4.setCellValue(major.getMjStopawdip());
        }
        hssfCell4.setCellStyle(dateCellStyle);
        HSSFCell hssfCell5 = row.createCell(15);
        if (major.getMjStopawnum() == null) {
          hssfCell5.setCellValue("");
        } else {
          hssfCell5.setCellValue(major.getMjStopawnum());
        }
        row.createCell(16).setCellValue(major.getMjState());
        row.createCell(17).setCellValue(major.getMjTotalcredit().toString());
        row.createCell(18).setCellValue(major.getMjGradcredit().toString());
        row.createCell(19).setCellValue(major.getMjTotalcourse().toString());
        row.createCell(20).setCellValue(major.getMjIfdiv());
        row.createCell(21).setCellValue(major.getMcId());
        row.createCell(22).setCellValue(major.getMjRegexplain());
        row.createCell(23).setCellValue(major.getMjGradexplain());
        HSSFCell hssfCell6 = row.createCell(24);
        if (major.getMjRemark() == null) {
          hssfCell6.setCellValue("");
        } else {
          hssfCell6.setCellValue(major.getMjRemark());
        }
      }
      headers = new HttpHeaders();
      headers.setContentDispositionFormData("attachment",
        new String("SCProvinceMajorTable.xls".getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
      headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
      baos = new ByteArrayOutputStream();
      workbook.write(baos);
    } catch (IOException e) {
      e.printStackTrace();
    }
    assert baos != null;
    return new ResponseEntity<>(baos.toByteArray(), headers, HttpStatus.CREATED);
  }

  /**
   * @return org.springframework.http.ResponseEntity<byte [ ]>
   * @author YHX
   * @description 导出全国课程的基本信息
   * @date 20:26 2019/7/30
   * @Param [courses]
   **/
  public static ResponseEntity<byte[]> exportNationalCourse2Excel(List<Nationwidecourse> courses) {
    HttpHeaders headers = null;
    ByteArrayOutputStream baos = null;
    try {
      //1.创建Excel文档
      HSSFWorkbook workbook = new HSSFWorkbook();
      //2.创建文档摘要
      workbook.createInformationProperties();
      //3.获取文档信息，并配置
      DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
      //3.1文档类别
      dsi.setCategory("全国课程信息");
      //3.2设置文档管理员
      dsi.setManager("Hachiko");
      //3.3设置组织机构
      dsi.setCompany("四川省成都市华迪软件信息有限公司");
      //4.获取摘要信息并配置
      SummaryInformation si = workbook.getSummaryInformation();
      //4.1设置文档主题
      si.setSubject("全国课程信息表");
      //4.2.设置文档标题
      si.setTitle("全国课程信息");
      //4.3 设置文档作者
      si.setAuthor("Hachiko");
      //4.4设置文档备注
      si.setComments("这不是完锐浩导出的一张表，错了就打他");
      //创建Excel表单
      HSSFSheet sheet = workbook.createSheet("全国课程信息表");
      //创建日期显示格式
      HSSFCellStyle dateCellStyle = workbook.createCellStyle();
      dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
      //创建标题的显示样式
      HSSFCellStyle headerStyle = workbook.createCellStyle();
      headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
      headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
      //定义列的宽度
      sheet.setColumnWidth(0, 8 * 256);
      sheet.setColumnWidth(1, 10 * 256);
      sheet.setColumnWidth(2, 12 * 256);
      //5.设置表头
      HSSFRow headerRow = sheet.createRow(0);
      HSSFCell cell0 = headerRow.createCell(0);
      cell0.setCellValue("国家课程代码");
      cell0.setCellStyle(headerStyle);
      HSSFCell cell1 = headerRow.createCell(1);
      cell1.setCellValue("课程名称");
      cell1.setCellStyle(headerStyle);
      HSSFCell cell2 = headerRow.createCell(2);
      cell2.setCellValue("学分");
      cell2.setCellStyle(headerStyle);
      //6.装数据
      for (int i = 0; i < courses.size(); i++) {
        HSSFRow row = sheet.createRow(i + 1);
        Nationwidecourse course = courses.get(i);
        row.createCell(0).setCellValue(course.getNcId());
        row.createCell(1).setCellValue(course.getNcName());
        row.createCell(2).setCellValue(course.getNcCredit().toString());
      }
      headers = new HttpHeaders();
      headers.setContentDispositionFormData("attachment",
        new String("NationwidecourseTable.xls".getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
      headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
      baos = new ByteArrayOutputStream();
      workbook.write(baos);
    } catch (IOException e) {
      e.printStackTrace();
    }
    assert baos != null;
    return new ResponseEntity<>(baos.toByteArray(), headers, HttpStatus.CREATED);
  }

  /**
   * @return org.springframework.http.ResponseEntity<byte [ ]>
   * @author YHX
   * @description 导出四川省的课程为Excel表
   * @date 16:55 2019/7/30
   * @Param [majors]
   **/
  public static ResponseEntity<byte[]> exportSCProvinceCourse2Excel(List<Course> courses) {
    HttpHeaders headers = null;
    ByteArrayOutputStream baos = null;
    try {
      //1.创建Excel文档
      HSSFWorkbook workbook = new HSSFWorkbook();
      //2.创建文档摘要
      workbook.createInformationProperties();
      //3.获取文档信息，并配置
      DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
      //3.1文档类别
      dsi.setCategory("四川省课程信息");
      //3.2设置文档管理员
      dsi.setManager("Hachiko");
      //3.3设置组织机构
      dsi.setCompany("四川省成都市华迪软件信息有限公司");
      //4.获取摘要信息并配置
      SummaryInformation si = workbook.getSummaryInformation();
      //4.1设置文档主题
      si.setSubject("四川省课程信息表");
      //4.2.设置文档标题
      si.setTitle("四川省课程信息");
      //4.3 设置文档作者
      si.setAuthor("Hachiko");
      //4.4设置文档备注
      si.setComments("这张表也不是完锐浩导出来，错了就锤死他");
      //创建Excel表单
      HSSFSheet sheet = workbook.createSheet("四川省课程信息表");
      //创建日期显示格式
      HSSFCellStyle dateCellStyle = workbook.createCellStyle();
      HSSFDataFormat format = workbook.createDataFormat();
      dateCellStyle.setDataFormat(format.getFormat("yyyy/mm/dd hh:mm:ss"));
      //创建标题的显示样式
      HSSFCellStyle headerStyle = workbook.createCellStyle();
      headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
      headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
      //设置宽度

      for (int i = 0; i < 18; i++) {
        sheet.setColumnWidth(i, 15 * 256);
      }
      //5.设置表头
      HSSFRow headerRow = sheet.createRow(0);
      HSSFCell cell0 = headerRow.createCell(0);
      cell0.setCellValue("课程编码");
      cell0.setCellStyle(headerStyle);
      HSSFCell cell1 = headerRow.createCell(1);
      cell1.setCellValue("专业计划版本");
      cell1.setCellStyle(headerStyle);
      HSSFCell cell2 = headerRow.createCell(2);
      cell2.setCellValue("国家课程代码");
      cell2.setCellStyle(headerStyle);
      HSSFCell cell3 = headerRow.createCell(3);
      cell3.setCellValue("教材国际书号");
      cell3.setCellStyle(headerStyle);
      HSSFCell cell4 = headerRow.createCell(4);
      cell4.setCellValue("课程名称");
      cell4.setCellStyle(headerStyle);
      HSSFCell cell5 = headerRow.createCell(5);
      cell5.setCellValue("课程说明");
      cell5.setCellStyle(headerStyle);
      HSSFCell cell6 = headerRow.createCell(6);
      cell6.setCellValue("试题来源");
      cell6.setCellStyle(headerStyle);
      HSSFCell cell7 = headerRow.createCell(7);
      cell7.setCellValue("课程状态");
      cell7.setCellStyle(headerStyle);
      HSSFCell cell8 = headerRow.createCell(8);
      cell8.setCellValue("学分");
      cell8.setCellStyle(headerStyle);
      HSSFCell cell9 = headerRow.createCell(9);
      cell9.setCellValue("及格线");
      cell9.setCellStyle(headerStyle);
      HSSFCell cell10 = headerRow.createCell(10);
      cell10.setCellValue("记分制");
      cell10.setCellStyle(headerStyle);
      HSSFCell cell11 = headerRow.createCell(11);
      cell11.setCellValue("主观题分数");
      cell11.setCellStyle(headerStyle);
      HSSFCell cell12 = headerRow.createCell(12);
      cell12.setCellValue("客观题分数");
      cell12.setCellStyle(headerStyle);
      HSSFCell cell13 = headerRow.createCell(13);
      cell13.setCellValue("总分");
      cell13.setCellStyle(headerStyle);
      HSSFCell cell14 = headerRow.createCell(14);
      cell14.setCellValue("考试时间");
      cell14.setCellStyle(headerStyle);
      HSSFCell cell15 = headerRow.createCell(15);
      cell15.setCellValue("是否过程性考核");
      cell15.setCellStyle(headerStyle);
      HSSFCell cell16 = headerRow.createCell(16);
      cell16.setCellValue("课程性质");
      cell16.setCellStyle(headerStyle);
      HSSFCell cell17 = headerRow.createCell(17);
      cell17.setCellValue("备注");
      cell17.setCellStyle(headerStyle);

      //6.装数据
      for (int i = 0; i < courses.size(); i++) {
        HSSFRow row = sheet.createRow(i + 1);
        Course course = courses.get(i);
        row.createCell(0).setCellValue(course.getCsId());
        row.createCell(1).setCellValue(course.getMvId());
        row.createCell(2).setCellValue(course.getNcId());
        row.createCell(3).setCellValue(course.getTbIsbn());
        row.createCell(4).setCellValue(course.getCsName());
        row.createCell(5).setCellValue(course.getCsIntro());
        row.createCell(6).setCellValue(course.getCsTqsource());
        row.createCell(7).setCellValue(course.getCsState());
        row.createCell(8).setCellValue(course.getCsCredit());
        row.createCell(9).setCellValue(course.getCsPassline());
        row.createCell(10).setCellValue(course.getCsPointsys());
        row.createCell(11).setCellValue(course.getCsSqmark());
        row.createCell(12).setCellValue(course.getCsOqmark());
        row.createCell(13).setCellValue(course.getCsTotalmark());
        HSSFCell examTimeCell = row.createCell(14);
        examTimeCell.setCellValue(course.getCsExamtime());
        examTimeCell.setCellStyle(dateCellStyle);
        row.createCell(15).setCellValue(course.getCsIfpa());
        row.createCell(16).setCellValue(course.getCsProperties());
        HSSFCell hssfCell = row.createCell(17);
        if (course.getCsRemark() == null) {
          hssfCell.setCellValue("");
        } else {
          hssfCell.setCellValue(course.getCsRemark());
        }
      }
      headers = new HttpHeaders();
      headers.setContentDispositionFormData("attachment",
        new String("SCProvinceMajorTable.xls".getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
      headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
      baos = new ByteArrayOutputStream();
      workbook.write(baos);
    } catch (IOException e) {
      e.printStackTrace();
    }
    assert baos != null;
    return new ResponseEntity<>(baos.toByteArray(), headers, HttpStatus.CREATED);
  }

}
